﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Catering.View_Operaction;
using System.Data;
using System.IO;

namespace Catering.Catering_Page.richang
{
    public partial class Repastpersondetail : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.txt_StartTime.Attributes.Add("onfocus", "WdatePicker({dateFmt:'yyyy'})");
                this.txt_EndTime.Attributes.Add("onfocus", "WdatePicker({dateFmt:'yyyy-MM-dd'})");
                loadPage();
            }
        }
        private void loadPage()
        {
            if (Session["User"] != null)
            {
                string userid = Session["PersonnelID"].ToString();
                int companyid = 0;
                int groupid = 0;
                int classid = 0;
                bool isAdmin = false;
                Bind_Data_to_Controls.GetCompanyByCurrentUser(userid, out companyid, out groupid, out classid, out isAdmin);
                Bind_Data_to_Controls.Bind_Area_ddl(ddl_Area, isAdmin, groupid, companyid);
                Bind_Data_to_Controls.Bind_Class_ddl(ddl_Area.SelectedItem, ddl_class, isAdmin, classid);
            }
        }

        protected void LinkButton1_Click(object sender, EventArgs e)
        {
            string datetype = this.Dr_DateType.SelectedItem.Value;
            string strstart = this.txt_StartTime.Text;
            string strend = this.txt_EndTime.Text;
            DateTime starttime = DateTime.Now;
            DateTime endtime = DateTime.Now;
            if (string.IsNullOrEmpty(strstart)) return;
            if (datetype.Equals("Other") && string.IsNullOrEmpty(strend)) return;
            if (datetype.Equals("Year"))
            {
                DateTime.TryParse(strstart + "-01-01 00:00:11", out starttime);
            }
            else
            {
                DateTime.TryParse(strstart, out starttime);
            }
            DateTime.TryParse(strend, out endtime);
            string classid = this.ddl_class.SelectedValue;
            string groupid = this.ddl_Area.SelectedValue;
            View_Monitor vm = new View_Monitor();
            string pid = "0";
            if (Session["UserID"] != null) pid = Session["UserID"].ToString();

            string areaID = "";                   //作业区
            string classID = "";                  //班组
            areaID = this.ddl_Area.SelectedItem.Value;               //作业区
            classID = this.ddl_class.SelectedItem.Value;             //班组

            DataTable dt = new DataTable();
            dt.Columns.Add("peicanrenshu", typeof(string));//配餐人数
            dt.Columns.Add("shoufeirenshu", typeof(string));//收费人数
            dt.Columns.Add("temId", typeof(string));//
            dt.Columns.Add("CName", typeof(string));//

            Dictionary<int, string> list = GetClassList(areaID, classID);

            if (datetype.Equals("Other"))
            {
                DataTable temp_shoufei = new Catering_Rules.DailyManagement().GetList("Date between '" +Convert.ToDateTime( txt_StartTime.Text) + "' and '" + Convert.ToDateTime(txt_EndTime.Text) + "'").Tables[0];
                DataTable temp_peican = new Catering_Rules.AttendanceRecord().GetList("Date between '" + Convert.ToDateTime(txt_StartTime.Text) + "' and '" + Convert.ToDateTime(txt_EndTime.Text) + "'").Tables[0];
                DataTable temp_shoufei1 = new Catering_Rules.Dynamics().GetList("Dates between '" + Convert.ToDateTime(txt_StartTime.Text) + "' and '" + Convert.ToDateTime(txt_EndTime.Text) + "'").Tables[0];
                int index = 0;
                foreach (int id in list.Keys)
                {                    
                    DataRow temprow = dt.NewRow();
                    temprow["temId"] = index+1;
                    temprow["CName"] = list[id];                   

                    //收费人数
                    DataRow[] tempsel_shoufei = temp_shoufei.Select(" ClassID='" + id + "'");
                    DataRow[] tempsel_shoufei1 = temp_shoufei1.Select(" ClassID='" + id + "'");
                    int shoufeirenshu = 0;
                    if (tempsel_shoufei != null && tempsel_shoufei.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei.Length; ismall++)
                        {
                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei[ismall]["DetermineNumber"].ToString());
                        }
                    }
                    if (tempsel_shoufei1 != null && tempsel_shoufei1.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei1.Length; ismall++)
                        {

                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei1[ismall]["Eat"].ToString());
                        }
                    }
                    temprow["shoufeirenshu"] = shoufeirenshu;
                    //配餐人数
                    DataRow[] tempsel_peican = temp_peican.Select(" ClassID='" + id + "' and State='1'");                    
                    if (tempsel_peican != null && tempsel_peican.Length > 0)
                    {
                        temprow["peicanrenshu"] = tempsel_peican.Length;                
                    }
                    dt.Rows.Add(temprow);
                    index++;
                }
            }
            else if (datetype.Equals("Year"))
            {
                DataTable temp_shoufei = new Catering_Rules.DailyManagement().GetList("Date like '%" + txt_StartTime.Text + "%'").Tables[0];
                DataTable temp_peican = new Catering_Rules.AttendanceRecord().GetList("Date like '%" + txt_StartTime.Text + "%'").Tables[0];
                DataTable temp_shoufei1 = new Catering_Rules.Dynamics().GetList("Dates like '%" + txt_StartTime.Text + "%'").Tables[0];
                int index = 0;
                foreach (int id in list.Keys)
                {
                    DataRow temprow = dt.NewRow();
                    temprow["temId"] = index + 1;
                    temprow["CName"] = list[id];

                    //收费人数
                    DataRow[] tempsel_shoufei = temp_shoufei.Select(" ClassID='" + id + "'");
                    DataRow[] tempsel_shoufei1 = temp_shoufei1.Select(" ClassID='" + id + "'");
                    int shoufeirenshu = 0;
                    if (tempsel_shoufei != null && tempsel_shoufei.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei.Length; ismall++)
                        {
                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei[ismall]["DetermineNumber"].ToString());                            
                        }                        
                    }
                    if (tempsel_shoufei1 != null && tempsel_shoufei1.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei1.Length; ismall++)                        
                        {
                            
                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei1[ismall]["Eat"].ToString());
                        }                        
                    }
                    temprow["shoufeirenshu"] = shoufeirenshu;
                    //配餐人数
                    DataRow[] tempsel_peican = temp_peican.Select(" ClassID='" + id + "' and State='1'");
                    if (tempsel_peican != null && tempsel_peican.Length > 0)
                    {
                        temprow["peicanrenshu"] = tempsel_peican.Length;
                    }
                    dt.Rows.Add(temprow);
                    index++;
                }
            }   
            else
            {
                endtime = starttime.AddMonths(1).AddDays(-1);
                DataTable temp_shoufei = new Catering_Rules.DailyManagement().GetList("Date between '" + starttime + "' and '" + endtime + "'").Tables[0];
                DataTable temp_peican = new Catering_Rules.AttendanceRecord().GetList("Date between '" + starttime + "' and '" + endtime + "'").Tables[0];
                DataTable temp_shoufei1 = new Catering_Rules.Dynamics().GetList("Dates between '" + starttime + "' and '" + endtime + "'").Tables[0];
                int index = 0;
                foreach (int id in list.Keys)
                {
                    DataRow temprow = dt.NewRow();
                    temprow["temId"] = index + 1;
                    temprow["CName"] = list[id];

                    //收费人数
                    DataRow[] tempsel_shoufei = temp_shoufei.Select(" ClassID='" + id + "'");
                    DataRow[] tempsel_shoufei1 = temp_shoufei1.Select(" ClassID='" + id + "'");
                    int shoufeirenshu = 0;
                    if (tempsel_shoufei != null && tempsel_shoufei.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei.Length; ismall++)
                        {
                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei[ismall]["DetermineNumber"].ToString());
                        }
                    }
                    if (tempsel_shoufei1 != null && tempsel_shoufei1.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei1.Length; ismall++)
                        {

                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei1[ismall]["Eat"].ToString());
                        }
                    }
                    temprow["shoufeirenshu"] = shoufeirenshu;

                    //配餐人数
                    DataRow[] tempsel_peican = temp_peican.Select(" ClassID='" + id + "' and State='1'");
                    if (tempsel_peican != null && tempsel_peican.Length > 0)
                    {
                        temprow["peicanrenshu"] = tempsel_peican.Length;
                    }
                    dt.Rows.Add(temprow);
                    index++;
                }
            }
            DataRow temprow_end = dt.NewRow();
            temprow_end["temId"] = "总计";
            temprow_end["CName"] = "";
            int peicanrenshu_end = 0;
            int shoufeirenshu_end = 0;
            for (int ismallend = 0; ismallend < dt.Rows.Count; ismallend++)
            {
                if (dt.Rows[ismallend]["peicanrenshu"].ToString() != "")
                    peicanrenshu_end += Convert.ToInt32(dt.Rows[ismallend]["peicanrenshu"].ToString());
                if (dt.Rows[ismallend]["shoufeirenshu"].ToString() != "")
                    shoufeirenshu_end += Convert.ToInt32(dt.Rows[ismallend]["shoufeirenshu"].ToString());
            }
            temprow_end["peicanrenshu"] = peicanrenshu_end;
            temprow_end["shoufeirenshu"] = shoufeirenshu_end;
            dt.Rows.Add(temprow_end);
            this.Rept_Rbzt.DataSource = dt;
            this.Rept_Rbzt.DataBind();
        }
        public Dictionary<int, string> GetClassList(string groupid, string classid)
        {
            Dictionary<int, string> result = new Dictionary<int, string>();
            if (!string.IsNullOrEmpty(groupid) && !groupid.Equals("-10"))
            {
                if (!string.IsNullOrEmpty(classid) && !classid.Equals("-10"))
                {
                    result.Add(Convert.ToInt32(classid), GetAllDDL_Class()[Convert.ToInt32(classid)]);
                    return result;
                }
                else
                {
                    return GetAllDDL_Class();
                }
            }
            else
            {
                Dictionary<int, string> temp = GetAllDDL_Area();
                List<Catering_Model.ClassInfo> listClass = new Catering_Rules.ClassInfo().GetAllList(0);
                if (temp.Count > 0)
                {
                    foreach (int id in temp.Keys)
                    {
                        foreach (Catering_Model.ClassInfo item in listClass.Where(c => c.AreaID == id))
                        {
                            result.Add(item.id, item.CName);
                        }
                    }
                }
            }
            return result;
        }

        protected void ddl_Area_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddl_Area.SelectedItem != null)
            {
                Bind_Data_to_Controls.Bind_Class_ddl(ddl_Area.SelectedItem, ddl_class);
            }
        }

        protected void Dr_DateType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (Dr_DateType.SelectedItem != null)
            {
                if (Dr_DateType.SelectedItem.Value.Equals("Year"))
                {
                    this.txt_StartTime.Attributes.Add("onfocus", "WdatePicker({dateFmt:'yyyy'})");
                    this.txt_StartTime.Text = "";
                    this.othertype01.Visible = false;
                    this.othertype02.Visible = false;
                }
                else if (Dr_DateType.SelectedItem.Value.Equals("Month"))
                {
                    this.txt_StartTime.Attributes.Add("onfocus", "WdatePicker({dateFmt:'yyyy-MM'})");
                    this.txt_StartTime.Text = "";
                    this.othertype01.Visible = false;
                    this.othertype02.Visible = false;
                }
                else
                {
                    this.txt_StartTime.Attributes.Add("onfocus", "WdatePicker({dateFmt:'yyyy-MM-dd'})");
                    this.txt_StartTime.Text = "";
                    this.othertype01.Visible = true;
                    this.othertype02.Visible = true;
                }
            }
        }
        public Dictionary<int, string> GetAllDDL_Area()
        {
            Dictionary<int, string> result = new Dictionary<int, string>();
            foreach (ListItem item in ddl_Area.Items)
            {
                if (!string.IsNullOrEmpty(item.Value) && !item.Value.Equals("-10"))
                {
                    result.Add(Convert.ToInt32(item.Value), item.Text);
                }
            }
            return result;
        }

        public Dictionary<int, string> GetAllDDL_Class()
        {
            Dictionary<int, string> result = new Dictionary<int, string>();
            foreach (ListItem item in ddl_class.Items)
            {
                if (!string.IsNullOrEmpty(item.Value) && !item.Value.Equals("-10"))
                {
                    result.Add(Convert.ToInt32(item.Value), item.Text);
                }
            }
            return result;
        }
        protected void but_Execl_Click(object sender, EventArgs e)
        {
            string strExcelName = "作业区: ";
            #region
            string datetype = this.Dr_DateType.SelectedItem.Value;
            string strstart = this.txt_StartTime.Text;
            string strend = this.txt_EndTime.Text;
            DateTime starttime = DateTime.Now;
            DateTime endtime = DateTime.Now;
            if (string.IsNullOrEmpty(strstart)) return;
            if (datetype.Equals("Other") && string.IsNullOrEmpty(strend)) return;
            if (datetype.Equals("Year"))
            {
                DateTime.TryParse(strstart + "-01-01 00:00:11", out starttime);
            }
            else
            {
                DateTime.TryParse(strstart, out starttime);
            }
            DateTime.TryParse(strend, out endtime);
            string classid = this.ddl_class.SelectedValue;
            string groupid = this.ddl_Area.SelectedValue;
            View_Monitor vm = new View_Monitor();
            string pid = "0";
            if (Session["UserID"] != null) pid = Session["UserID"].ToString();

            string areaID = "";                   //作业区
            string classID = "";                  //班组
            areaID = this.ddl_Area.SelectedItem.Value;               //作业区
            strExcelName += ddl_Area.SelectedItem.Text+" 班组:";
            classID = this.ddl_class.SelectedItem.Value;             //班组
            strExcelName += ddl_class.SelectedItem.Text+" ";

            DataTable dt = new DataTable();
            dt.Columns.Add("peicanrenshu", typeof(string));//配餐人数
            dt.Columns.Add("shoufeirenshu", typeof(string));//收费人数
            dt.Columns.Add("temId", typeof(string));//
            dt.Columns.Add("CName", typeof(string));//

            Dictionary<int, string> list = GetClassList(areaID, classID);

            if (datetype.Equals("Other"))
            {
                strExcelName += txt_StartTime.Text + "到" + txt_EndTime.Text + "就餐人员明细";
                DataTable temp_shoufei = new Catering_Rules.DailyManagement().GetList("Date between '" + Convert.ToDateTime(txt_StartTime.Text) + "' and '" + Convert.ToDateTime(txt_EndTime.Text) + "'").Tables[0];
                DataTable temp_peican = new Catering_Rules.AttendanceRecord().GetList("Date between '" + Convert.ToDateTime(txt_StartTime.Text) + "' and '" + Convert.ToDateTime(txt_EndTime.Text) + "'").Tables[0];
                DataTable temp_shoufei1 = new Catering_Rules.Dynamics().GetList("Dates between '" + Convert.ToDateTime(txt_StartTime.Text) + "' and '" + Convert.ToDateTime(txt_EndTime.Text) + "'").Tables[0];
                int index = 0;
                foreach (int id in list.Keys)
                {
                    DataRow temprow = dt.NewRow();
                    temprow["temId"] = index + 1;
                    temprow["CName"] = list[id];

                    //收费人数
                    DataRow[] tempsel_shoufei = temp_shoufei.Select(" ClassID='" + id + "'");
                    DataRow[] tempsel_shoufei1 = temp_shoufei1.Select(" ClassID='" + id + "'");
                    int shoufeirenshu = 0;
                    if (tempsel_shoufei != null && tempsel_shoufei.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei.Length; ismall++)
                        {
                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei[ismall]["DetermineNumber"].ToString());
                        }
                    }
                    if (tempsel_shoufei1 != null && tempsel_shoufei1.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei1.Length; ismall++)
                        {

                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei1[ismall]["Eat"].ToString());
                        }
                    }
                    temprow["shoufeirenshu"] = shoufeirenshu;
                    //配餐人数
                    DataRow[] tempsel_peican = temp_peican.Select(" ClassID='" + id + "' and State='1'");
                    if (tempsel_peican != null && tempsel_peican.Length > 0)
                    {
                        temprow["peicanrenshu"] = tempsel_peican.Length;
                    }
                    dt.Rows.Add(temprow);
                    index++;
                }
            }
            else if (datetype.Equals("Year"))
            {
                strExcelName += txt_StartTime.Text + "年"  + "就餐人员明细";
                DataTable temp_shoufei = new Catering_Rules.DailyManagement().GetList("Date like '%" + txt_StartTime.Text + "%'").Tables[0];
                DataTable temp_peican = new Catering_Rules.AttendanceRecord().GetList("Date like '%" + txt_StartTime.Text + "%'").Tables[0];
                DataTable temp_shoufei1 = new Catering_Rules.Dynamics().GetList("Dates like '%" + txt_StartTime.Text + "%'").Tables[0];
                int index = 0;
                foreach (int id in list.Keys)
                {
                    DataRow temprow = dt.NewRow();
                    temprow["temId"] = index + 1;
                    temprow["CName"] = list[id];

                    //收费人数
                    DataRow[] tempsel_shoufei = temp_shoufei.Select(" ClassID='" + id + "'");
                    DataRow[] tempsel_shoufei1 = temp_shoufei1.Select(" ClassID='" + id + "'");
                    int shoufeirenshu = 0;
                    if (tempsel_shoufei != null && tempsel_shoufei.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei.Length; ismall++)
                        {
                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei[ismall]["DetermineNumber"].ToString());
                        }
                    }
                    if (tempsel_shoufei1 != null && tempsel_shoufei1.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei1.Length; ismall++)
                        {

                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei1[ismall]["Eat"].ToString());
                        }
                    }
                    temprow["shoufeirenshu"] = shoufeirenshu;
                    //配餐人数
                    DataRow[] tempsel_peican = temp_peican.Select(" ClassID='" + id + "' and State='1'");
                    if (tempsel_peican != null && tempsel_peican.Length > 0)
                    {
                        temprow["peicanrenshu"] = tempsel_peican.Length;
                    }
                    dt.Rows.Add(temprow);
                    index++;
                }
            }
            else
            {
                strExcelName += txt_StartTime.Text +"月"+ "就餐人员明细";
                endtime = starttime.AddMonths(1).AddDays(-1);
                DataTable temp_shoufei = new Catering_Rules.DailyManagement().GetList("Date between '" + starttime + "' and '" + endtime + "'").Tables[0];
                DataTable temp_peican = new Catering_Rules.AttendanceRecord().GetList("Date between '" + starttime + "' and '" + endtime + "'").Tables[0];
                DataTable temp_shoufei1 = new Catering_Rules.Dynamics().GetList("Dates between '" + starttime + "' and '" + endtime + "'").Tables[0];
                int index = 0;
                foreach (int id in list.Keys)
                {
                    DataRow temprow = dt.NewRow();
                    temprow["temId"] = index + 1;
                    temprow["CName"] = list[id];

                    //收费人数
                    DataRow[] tempsel_shoufei = temp_shoufei.Select(" ClassID='" + id + "'");
                    DataRow[] tempsel_shoufei1 = temp_shoufei1.Select(" ClassID='" + id + "'");
                    int shoufeirenshu = 0;
                    if (tempsel_shoufei != null && tempsel_shoufei.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei.Length; ismall++)
                        {
                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei[ismall]["DetermineNumber"].ToString());
                        }
                    }
                    if (tempsel_shoufei1 != null && tempsel_shoufei1.Length > 0)
                    {
                        for (int ismall = 0; ismall < tempsel_shoufei1.Length; ismall++)
                        {

                            shoufeirenshu += Convert.ToInt32(tempsel_shoufei1[ismall]["Eat"].ToString());
                        }
                    }
                    temprow["shoufeirenshu"] = shoufeirenshu;

                    //配餐人数
                    DataRow[] tempsel_peican = temp_peican.Select(" ClassID='" + id + "' and State='1'");
                    if (tempsel_peican != null && tempsel_peican.Length > 0)
                    {
                        temprow["peicanrenshu"] = tempsel_peican.Length;
                    }
                    dt.Rows.Add(temprow);
                    index++;
                }
            }
            DataRow temprow_end = dt.NewRow();
            temprow_end["temId"] = "总计";
            temprow_end["CName"] = "";
            int peicanrenshu_end = 0;
            int shoufeirenshu_end = 0;
            for (int ismallend = 0; ismallend < dt.Rows.Count; ismallend++)
            {
                if (dt.Rows[ismallend]["peicanrenshu"].ToString() != "")
                    peicanrenshu_end += Convert.ToInt32(dt.Rows[ismallend]["peicanrenshu"].ToString());
                if (dt.Rows[ismallend]["shoufeirenshu"].ToString() != "")
                    shoufeirenshu_end += Convert.ToInt32(dt.Rows[ismallend]["shoufeirenshu"].ToString());
            }
            temprow_end["peicanrenshu"] = peicanrenshu_end;
            temprow_end["shoufeirenshu"] = shoufeirenshu_end;
            dt.Rows.Add(temprow_end);
            #endregion
            
            
            
            
                
            Microsoft.Office.Interop.Excel.Application excel1 = null;
            Microsoft.Office.Interop.Excel._Worksheet ws = null;
            Microsoft.Office.Interop.Excel.Range canbie = null;
            string stick = DateTime.Now.Ticks.ToString();

            try
            {
                excel1 = new Microsoft.Office.Interop.Excel.Application();
                excel1.Application.Workbooks.Add(true);//引用Excel工作簿
                //Microsoft.Office.Interop.Excel._Worksheet ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
                ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
                ws = (Microsoft.Office.Interop.Excel._Worksheet)excel1.ActiveSheet;//获取当前活动工作薄
                ////Microsoft.Office.Interop.Excel.Range canbie;//创建Range操作一行作为标题
                excel1.Visible = false;

                #region  表头


                canbie = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 4]);
                canbie.MergeCells = true;//合并单元格
                canbie.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中
                canbie.Font.Bold = 2;
                ws.Cells[1, 1] = strExcelName;

                #endregion

                #region 第一行标题
                ws.Cells[2, 1] = "序号";
                ws.Cells[2, 2] = "班组名称";
                ws.Cells[2, 3] = "收费人员";
                ws.Cells[2, 4] = "配餐人员";

                #endregion

                #region 内容

                int canbiehangshu = 3;//行数
                if (dt != null && dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        ws.Cells[canbiehangshu, 1] = dt.Rows[i]["temId"].ToString();
                        ws.Cells[canbiehangshu, 2] = dt.Rows[i]["CName"].ToString();
                        ws.Cells[canbiehangshu, 3] = dt.Rows[i]["shoufeirenshu"].ToString();
                        ws.Cells[canbiehangshu, 4] = dt.Rows[i]["peicanrenshu"].ToString();
                        canbiehangshu++;
                    }
                    dt.Dispose();
                }
                ws.Cells.EntireColumn.AutoFit();
                canbie = ws.get_Range(ws.Cells[2, 1], ws.Cells[canbiehangshu - 1, 4]);
                canbie.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中
                canbie.Borders.LineStyle = 1;

                #endregion

                if (!Directory.Exists(Server.MapPath("../../xls_template"))) Directory.CreateDirectory(Server.MapPath("../../xls_template"));
                ws.SaveAs(Server.MapPath("../../xls_template") + "\\" + stick + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlAddIn8);
            }
            finally
            {
                if (excel1 != null)
                {
                    if (ws != null)
                    {
                        if (canbie != null)
                        {
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(canbie);
                            canbie = null;

                        }
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
                        ws = null;
                    }
                    excel1.Application.Workbooks.Close();
                    excel1.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
                    Toolss.KillExcel(excel1);
                    excel1 = null;
                    GC.Collect();
                }
            }

            #region 发送

            try
            {
                string path = Server.MapPath("../../xls_template") + "\\" + stick + ".xls";
                System.IO.FileInfo file = new System.IO.FileInfo(path);
                Response.Clear();
                Response.Charset = "GB2312";
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                // 添加头信息，,弹出另存为窗口  
                Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
                //让浏览器头显示下载信息  
                Response.AddHeader("Content-Length", file.Length.ToString());
                // 指定返回一个不能被客户端读取的流，下载  
                Response.ContentType = "application/ms-excel";
                //把文件流下载到客户端  
                Response.WriteFile(file.FullName);
                // 停止页面的执行
                Response.End();
            }
            catch
            {
            }

            #endregion
            
        }
    }
}